# encoding:utf-8
import os
import configparser
import time
import traceback
import pymysql
# 用于忽略表已存在的警告
import warnings
class Save():
    '''
    用于保存数据
    '''

    def __init__(self):
        # 获取项目根目录（这里假设当前脚本所在目录往上一级为根目录，可根据实际情况调整）
        root_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        config_file_path = os.path.join(root_dir, 'config.ini')
        config = configparser.ConfigParser()
        config.read(config_file_path)
        self._config = config
        pass

    # 清理mysql数据
    def _delete_mysql(self):
        import pymysql
        # 用于忽略表已存在的警告
        import warnings
        warnings.filterwarnings("ignore")
        host = self._config.get('mysql', 'host')
        port = self._config.getint('mysql', 'port')
        user = self._config.get('mysql', 'user')
        passwd = self._config.get('mysql', 'passwd')
        db = self._config.get('mysql', 'db')

        conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset='utf8')
        cursor = conn.cursor()
        timestring = time.strftime('%Y%m%d%H', time.localtime(time.time()))
        tablename = 'T' + timestring + 'TheFutureOfHome'
        drop_sql = """drop table IF EXISTS %s""" % (tablename)
        drop_rows = cursor.execute(drop_sql)
        print('delete ' + str(drop_rows) + ' rows.')

        conn.commit()
        cursor.close()
        conn.close()

    # 保存到mysql
    def _save_mysql(self,data,table):

        warnings.filterwarnings("ignore")
        host = self._config.get('mysql', 'host')
        port = self._config.getint('mysql', 'port')
        user = self._config.get('mysql', 'user')
        passwd = self._config.get('mysql', 'passwd')
        db = self._config.get('mysql', 'db')

        conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset='utf8')
        cursor = conn.cursor()

        #table = 'tapd_story_list'
        keys = ', '.join(data.keys())
        values = ', '.join(['%s'] * len(data))
        sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE '.format(table=table,keys=keys,values=values)
        update = ','.join([" {key} = %s".format(key=key) for key in data])
        sql += update
        try:
            print(f"Executing SQL: {sql} with parameters {tuple(data.values()) * 2}")
            cursor.execute(sql, tuple(data.values()) * 2)
            conn.commit()
        except:
            print("error datalinelist:" + str(data))
            with open('insert_error_stock_list.txt', 'a+', encoding='utf-8') as f2:
                f2.writelines(str(data))
                f2.close()
            traceback.print_exc()
            conn.rollback()

        cursor.close()
        conn.close()

    def connect(self):
        warnings.filterwarnings("ignore")
        host = self._config.get('mysql', 'host')
        port = self._config.getint('mysql', 'port')
        user = self._config.get('mysql', 'user')
        passwd = self._config.get('mysql', 'passwd')
        db = self._config.get('mysql', 'db')

        conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset='utf8')
        return conn
        #cursor = conn.cursor()

    def close(self, conn,cursor):
        cursor.close()
        conn.close()

    def exec(self,data,table,conn,cursor):
        keys = ', '.join(data.keys())
        values = ', '.join(['%s'] * len(data))
        sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE '.format(table=table,keys=keys,values=values)
        update = ','.join([" {key} = %s".format(key=key) for key in data])
        sql += update
        try:
            #print(f"Executing SQL: {sql} with parameters {tuple(data.values()) * 2}")
            cursor.execute(sql, tuple(data.values()) * 2)
            conn.commit()
        except:
            print("error datalinelist:" + str(data))
            with open('insert_error_stock_list.txt', 'a+', encoding='utf-8') as f2:
                f2.writelines(str(data)+"\n")
                f2.close()
            traceback.print_exc()
            conn.rollback()
